package com.lovo.sgproj.dao.imp;

import com.lovo.sgproj.bean.ClassBean;
import com.lovo.sgproj.bean.RoomBean;
import com.lovo.sgproj.bean.StudentBean;
import com.lovo.sgproj.dao.RoomDAO;
import com.lovo.sgproj.util.DBUtil;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

@Component
public class RoomDAOImp implements RoomDAO {
    @Override
    public ArrayList<RoomBean> findAllRoom() {
        ArrayList<RoomBean> roomLst = new ArrayList<>();
        String sql = "select pk_roomid,f_address,f_maxpeople,f_peoplenow,f_rent,f_roomstatus,f_owner,f_ownerphone,f_roomtype from t_room";
        try (Connection con = DBUtil.getConnection();
             Statement st = con.createStatement()) {
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                RoomBean room = new RoomBean();
                room.setRoomID(rs.getInt("pk_roomid"));
                room.setAddress(rs.getString("f_address"));
                room.setMaxpeople(rs.getInt("f_maxpeople"));
                room.setPeoplenow(rs.getInt("f_peoplenow"));
                room.setRent(rs.getDouble("f_rent"));
                room.setRoomstatus(rs.getBoolean("f_roomstatus"));
                room.setOwner(rs.getString("f_owner"));
                room.setOwnerphone(rs.getString("f_ownerphone"));
                room.setRoomtype(rs.getBoolean("f_roomtype"));

                roomLst.add(room);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return roomLst;
    }

    @Override
    public ArrayList<RoomBean> findAllUserableRoom(boolean roomType) {
        ArrayList<RoomBean> roomLst = new ArrayList<>();
        String sql = "select f_address,pk_roomid from t_room where f_roomtype=? and f_peoplenow <= f_maxpeople";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {

            int sex = 0;
            if (roomType == true) {
                sex = 1;
            }

            ps.setInt(1, sex);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean room = new RoomBean();
                room.setAddress(rs.getString("f_address"));
                room.setRoomID(rs.getInt("pk_roomid"));
                roomLst.add(room);
            }
            return roomLst;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;

    }

    @Override
    public int getRoomNumByAddress(String roomAddress) {
        int num = -1;
        String sql = "SELECT COUNT(f_address) FROM t_room WHERE f_address=?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, roomAddress);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                num = rs.getInt("COUNT(f_address)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public void insertRoom(RoomBean room) {
        String sql = "insert into t_room(f_address,f_maxpeople,f_peoplenow,f_rent,f_owner,f_roomstatus,f_roomshape,f_roomtype,f_pay,f_ownerphone,f_roomdate) values " +
                "(?,?,?,?,?,?,?,?,?,?,?)";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, room.getAddress());
            ps.setInt(2, room.getMaxpeople());
            ps.setInt(3, 0);
            ps.setDouble(4, room.getRent());
            ps.setString(5, room.getOwner());
            if (room.getRoomstatus()) {
                ps.setInt(6, 1);
            } else {
                ps.setInt(6, 0);
            }
            ps.setString(7, room.getRoomshape());
            if (room.getRoomtype()) {
                ps.setInt(8, 1);
            } else {
                ps.setInt(8, 0);
            }
            ps.setString(9, room.getPay());
            ps.setString(10, room.getOwnerphone());
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM--dd");
            java.sql.Date timePara = null;
            try {
                timePara = new java.sql.Date(room.getRoomdate().getTime());
                ps.setDate(11, timePara);
                ps.executeUpdate();

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteRoomByID(int roomID) {
        String sql = "delete from t_room where pk_roomid = ?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, roomID);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public RoomBean getRoomAllDetailByID(int roomID) {
        RoomBean room = null;
        String sql = "select f_address,f_maxpeople,f_peoplenow,f_rent,f_owner,f_roomstatus,f_roomshape,f_roomtype,f_pay,f_ownerphone,f_roomdate," +
                "f_stuname,f_phone,f_classname " +
                "from t_room left join t_student on pk_roomid=fk_roomid left join t_class on fk_classid=pk_classid  where pk_roomid=?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, roomID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                if (room == null) {
                    room = new RoomBean();
                    room.setAddress(rs.getString("f_address"));
                    room.setMaxpeople(rs.getInt("f_maxpeople"));
                    room.setPeoplenow(rs.getInt("f_peoplenow"));
                    room.setRent(rs.getDouble("f_rent"));
                    room.setOwner(rs.getString("f_owner"));
                    room.setRoomstatus(rs.getBoolean("f_roomstatus"));
                    room.setRoomshape(rs.getString("f_roomshape"));
                    room.setRoomtype(rs.getBoolean("f_roomtype"));
                    room.setPay(rs.getString("f_pay"));
                    room.setOwnerphone(rs.getString("f_ownerphone"));
                    room.setRoomdate(rs.getDate("f_roomdate"));
                }

                StudentBean student = new StudentBean();
                student.setStudentname(rs.getString("f_stuname"));
                student.setStudentphone(rs.getString("f_phone"));

                ClassBean theClass = new ClassBean();
                theClass.setClassName(rs.getString("f_classname"));

                student.setTheClass(theClass);
                room.getStuLst().add(student);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return room;
    }

    @Override
    public ArrayList<RoomBean> findAviableRoom(String address, int roomStatus, int availableLive, int roomType) {

/*        String sql = "select pk_roomid,f_address,f_maxpeople,f_peoplenow,f_rent,f_roomstatus,f_owner,f_ownerphone,f_roomtype from t_room where f_address like ? " +
                "and f_roomstatus=? and f_maxpeople>f_peoplenow and f_roomtype=?";*/
        String sql = "select pk_roomid,f_address,f_maxpeople,f_peoplenow,f_rent,f_roomstatus,f_owner,f_ownerphone,f_roomtype from t_room where f_address like ? ";

        if (roomStatus == -1 || availableLive == -1 || roomType == -1) {
            sql += "";
        }
       /* if (availableLive == -1) {
            sql += "";
        }
        if (roomType == -1) {
            sql += "";
        }*/
        if (roomStatus == 1) {
            sql += " and f_roomstatus=1 ";
        } else if (roomStatus == 0) {
            sql += " and f_roomstatus=0 ";
        }
        if (availableLive == 0) {
            sql += " and f_maxpeople>f_peoplenow ";
        }
        if (roomType == 1) {
            sql += " and f_roomtype=1 ";
        } else if (roomType == 0) {
            sql += " and f_roomtype=0 ";
        }

        ArrayList<RoomBean> roomLst = new ArrayList<>();
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, "%" + address + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean room = new RoomBean();
                room.setRoomID(rs.getInt("pk_roomid"));
                room.setAddress(rs.getString("f_address"));
                room.setMaxpeople(rs.getInt("f_maxpeople"));
                room.setPeoplenow(rs.getInt("f_peoplenow"));
                room.setRent(rs.getDouble("f_rent"));
                room.setRoomstatus(rs.getBoolean("f_roomstatus"));
                room.setOwner(rs.getString("f_owner"));
                room.setOwnerphone(rs.getString("f_ownerphone"));
                room.setRoomtype(rs.getBoolean("f_roomtype"));

                roomLst.add(room);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return roomLst;
    }

    @Override
    public ArrayList<RoomBean> findChangeableRoom(StudentBean stu) {
        ArrayList<RoomBean> roomLst = new ArrayList<>();
        String sql = "select pk_roomid,f_address,f_maxpeople,f_peoplenow,f_rent,f_roomstatus,f_owner,f_ownerphone,f_roomtype from t_room where f_roomtype = ? and f_maxpeople > f_peoplenow and pk_roomid != ?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            boolean flag = stu.getGender();
            int num = 0;
            if (flag) {
                num = 1;
            }
            ps.setInt(1, num);
            ps.setInt(2, stu.getTheRoom().getRoomID());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean room = new RoomBean();
                room.setRoomID(rs.getInt("pk_roomid"));
                room.setAddress(rs.getString("f_address"));
                room.setMaxpeople(rs.getInt("f_maxpeople"));
                room.setPeoplenow(rs.getInt("f_peoplenow"));
                room.setRent(rs.getDouble("f_rent"));
                room.setRoomstatus(rs.getBoolean("f_roomstatus"));
                room.setOwner(rs.getString("f_owner"));
                room.setOwnerphone(rs.getString("f_ownerphone"));
                room.setRoomtype(rs.getBoolean("f_roomtype"));
                roomLst.add(room);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return roomLst;
    }
}
